package com.project.dao.impl;

import com.project.annotation.Column;
import com.project.annotation.Id;
import com.project.annotation.Table;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class BaseDao {
    protected Connection con;
    protected PreparedStatement ps;
    protected ResultSet rs;

    /**
     * 建立连接
     */
    public void setConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            this.con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:12345/user?characterEncoding=utf-8",
                    "root", "lovo");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        BaseDao dao = new BaseDao();
        dao.setConnection();
        System.out.println(dao.con);
    }
    /**
     * 关闭连接
     */
    public void closeConnection() {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 更新数据库（修改和删除）
     * @param sql
     * @param valueArray 值列表（占位符的值）
     */
    public void updateDB(String sql,Object... valueArray){
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);
            //给占位符填值
            for (int i = 0 ; i < valueArray.length; i++){
                ps.setObject(i+1,valueArray[i]);
            }
            ps.executeUpdate();

        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        finally {
            this.closeConnection();
        }
    }

    /**
     * 执行查询
     *
     * @param sql        查询SQL语句
     * @param beanClass  查询结果需要封装的实体对象
     * @param valueArray 查询条件值列表
     * @return 实体类集合
     */
    public List findDB(String sql, Class beanClass, Object... valueArray) {
        List list = new ArrayList();
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);
            for (int i = 0; i < valueArray.length; i++) {
                ps.setObject(i + 1, valueArray[i]);
            }

            rs = ps.executeQuery();
            //得到结果集信息对象
            ResultSetMetaData rm = rs.getMetaData();
            //得到查询列的数量
            int columnCount = rm.getColumnCount();

            while (rs.next()) {
                //产生实体类的对象
                Object beanObj = beanClass.getConstructor().newInstance();

                for (int i = 1; i <= columnCount; i++) {
                    //得到查询列名
                    String columnName = rm.getColumnName(i);
                    //从结果集中，得到指定列名对应的数据
                    Object valueObj = rs.getObject(columnName);

                    //根据列名得到属性对象
                    Field f = this.getFieldByColumn(beanClass, columnName);
                    //根据列名，得到方法名
                    String methodName = getMethodName(f.getName());


                    if (f.getType() == LocalDate.class) {
                        valueObj = LocalDate.parse(valueObj.toString());
                    }
                    //得到方法对象
                    Method m = beanClass.getMethod(methodName, f.getType());
                    //执行方法，传入实参
                    m.invoke(beanObj, valueObj);
                }

                list.add(beanObj);
            }
        } catch (Exception throwables) {
            throwables.printStackTrace();
        } finally {
            this.closeConnection();
        }


        return list;
    }


    public void addObj(Object beanObj){
        String sql = "";
        //值列表集合
        List valueList = new ArrayList();

        try {
            //获取sql语句，设置值列表集合
            sql = this.getInsertSql(beanObj,valueList);
        } catch (Exception e) {
            e.printStackTrace();
        }
        this.setConnection();
        try {
            ps = con.prepareStatement(sql);

            for (int i = 0; i < valueList.size(); i++){
                //填充占位符
                ps.setObject(i+1,valueList.get(i));
            }
            ps.executeUpdate();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }
        finally {
            this.closeConnection();
        }

    }

    /**
     * 得到添加SQL语句
     *
     * @param beanObj 添加实体类对象
     * @return SQL语句
     */
    private String getInsertSql(Object beanObj, List valueList) throws Exception {
        String tableName = "";
        //得到实体类的类模版
        Class beanClass = beanObj.getClass();
        //判断类中是否有Table注解。
        if (beanClass.isAnnotationPresent(Table.class)) {
            Table table = (Table) beanClass.getAnnotation(Table.class);
            tableName = table.value();
        }

        String sql = "insert into " + tableName + "(";
        //得到属性列表
        Field[] farray = beanClass.getDeclaredFields();
        for (Field f : farray) {
            //判断属性是否拥有Column注解和Id注解
            if (f.isAnnotationPresent(Column.class) &&
                    f.isAnnotationPresent(Id.class) == false) {
                //取出属性注解描述的列名，拼接SQL语句
                Column column = f.getAnnotation(Column.class);

                sql += column.value() + ",";

                //拼接一个列，给值列表集合添加一个值
                f.setAccessible(true);
                valueList.add(f.get(beanObj));
            }
        }
        //去掉最后一个逗号
        sql = sql.substring(0, sql.length() - 1) + ") values(";
        for (int i = 0; i < valueList.size(); i++) {
            sql += "?,";
        }
        sql = sql.substring(0, sql.length() - 1) + ")";


        return sql;
    }

    /**
     * 根据列名，得到属性对象
     *
     * @param beanClass  实体类类模版
     * @param columnName 列名
     * @return 属性对象
     */
    private Field getFieldByColumn(Class beanClass, String columnName) {
        // 得到该类的属性列表
        Field[] farray = beanClass.getDeclaredFields();
        for (Field f : farray) {
            //判断属性是否有column注解
            if (f.isAnnotationPresent(Column.class)) {
                //取出属性的column注解
                Column c = f.getAnnotation(Column.class);
                if (c.value().equals(columnName)) {
                    return f;
                }
            }
        }

        return null;
    }

    /**
     * 根据列名，得到方法名
     *
     * @param columnName 列名
     * @return 方法名
     */
    private String getMethodName(String columnName) {
        //将首字母变成大写
        String s = columnName.substring(0, 1).toUpperCase();

        return "set" + s + columnName.substring(1);
    }

}
